import requests
from requests.compat import urljoin
from bs4 import BeautifulSoup
import re
import xlsxwriter
import time

def CleanData(InfoString):
    # 除去数据中的\n、\r、空格，以及变换日期格式
    InfoString = InfoString.replace('\n', '',)  # 这里爬下来的文本里面有很多\n\r的字符，把它们去掉
    InfoString = InfoString.replace('\r', '',)  # 第三个参数默认，替换全部
    InfoString = InfoString.replace(' ', '',)
    InfoString = InfoString.replace('年', '-', 1)  # 日期格式调整
    InfoString = InfoString.replace('月', '-', 1)
    InfoString = InfoString.replace('日', '', 1)
    return InfoString

def ExtractBJWeather():
    # 爬取成都天气数据
    BJWeatherExcel = xlsxwriter.Workbook('成都天气质量.xlsx')
        # 创建Excel表格成都天气质量爬虫.xlsx
    url_prefix = 'http://www.tianqihoubao.com/aqi/chengdu'
    # url = 'http://www.tianqihoubao.com/lishi/chengdu.html'
    head = {'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/83.0.4103.106 Safari/537.36','Connection': 'close'}

    # 日期	AQI	质量等级	PM2.5	PM10	SO2	CO	NO2	O3_8h
    # 2013-12-02	142	轻度污染	109	138	61	2.6	88	11
    tableHeader = ["日期", "质量等级", "AQI", "当天AQI排名", "PM2.5", "PM10", "SO2", "NO2", "CO", "O3_8h"]
    
    WorkSheet = BJWeatherExcel.add_worksheet("Sheet1")
    row = 0
    col = 0
            
    for i in range(len(tableHeader)):
        WorkSheet.write(row, col+i, tableHeader[i])  # 表格第一行
    
    for year in range(2014,2022):
        for month in range(1,13):
            if month<10:
                month = "0" + str(month)
            url = url_prefix + "-" + str(year) + str(month) + ".html"
            print(url)
            try: # 请求过度频繁的处理
                AMonth_HTML = requests.get(url, headers = head)
                # print(str(AMonth_HTML.content))
                AMonthObj = BeautifulSoup(AMonth_HTML.content, 'lxml')  # 网页请求
                AMonthData = AMonthObj.find_all('tr')  # 表格中的一行,即为某一天的天气数据，存储一个月的所有行
                del(AMonthData[0]) # 去除表头
                for ADay in AMonthData:  # 遍历一个月数据的所有行，ADay即为一行
                    ALine = ADay.find_all('td') # 找出表格一行内容
                    col = 0 # 从0列开始写入
                    row += 1 # 比起上一次写入时，行+1
                    for info in ALine: # 遍历行的四项信息
                        AnInfo = str(info.get_text())  # 获取行数据下的文本内容
                        AnInfo = CleanData(AnInfo) # 除去数据中的\n、\r、空格，以及变换日期格式
                        WorkSheet.write(row, col, AnInfo) # 数据写入表格
                        col += 1 # 列增加
            except:  # 请求过快时，按5s休息处理
                print("requests speed so high,need sleep!")
                time.sleep(5)
                print("continue...")
                continue
            print("page over")

    BJWeatherExcel.close()
    print('---------------------end----------------')
    return 
if __name__ == '__main__':
    ExtractBJWeather()   